﻿<?xml version="1.0" encoding="utf-8" ?>
<AdoNetDataAccess connection="$ArticleDBConnection$" formatter="bizobjlist of Article(author,primaryPicture)">
	<FormatterInfo ArticleFieldType="columnName">
		<code for="postEach" on="Article">
					<![CDATA[
			try
			{
				string prefx =App.appData["MediaCacheNamePrefix"]!=null ? App.appData["MediaCacheNamePrefix"].ToString() : "img";
				CurrentBizObj.put("mediaCacheName",string.Format("{0}{1}",prefx,CurrentBizObj.get("mediaCacheName")));
			}
			catch(Exception e)
			{
				string filename= string.Format(@"{0}error.txt",App.resMgr.getMacro("BaseArticleStore"));
				System.IO.File.WriteAllText(filename,string.Format("{0}:{1}",e.Message,(e.InnerException!=null) ? e.InnerException.Message : ""));
			}
		]]></code>

	</FormatterInfo>
		<Sql>
			Select
			a.id
			,a.translatedTitles
			,a.translatedSummaries
			,a.translationDetails
			,a.baseTitle,a.uniqueTitle,a.summary as baseSummary,a.keyword
			,a.mediaId,a.dateUpdated,a.dateCreated
			,u.lastName as authorlastName
			,u.id as authorid
			,u.firstName as authorfirstName
			,u.userName as authorusername
			,p.id as primaryPictureId
			,p.name as primaryPictureName
			,p.fileName as primaryPictureFileName
			,p.caption as primaryPictureCaption,
			t.objectId, t.parentId, t.parentHostId, t.keyName, t.depth, t.rootId
			FROM Articles a LEFT JOIN USERS u ON u.id=a.authorId
			LEFT JOIN Pictures p on p.id = a.id
			INNER JOIN ObjectTreeNodes t ON a.companyId = t.parentHostId
			WHERE t.objectId=@companyId

			<code><![CDATA[
				string firstPerc = "", secondPerc = "", c="x";
				if(criteria!=null){
					IBizObjList list = criteria.get("filterOptions") as IBizObjList;
					int i = 0;
					if(list!=null && list.numElements > 0)
						sql.Append(" AND ");
					foreach(IBizObj f in list)
					{
						i++;
						c = f.get("criteria") as string;
						if (string.Compare(c, "s", true) == 0)
							firstPerc = "%";
						else if (string.Compare(c, "e", true) == 0)
							secondPerc = "%";
						else if (string.Compare(c, "c", true) == 0)
							firstPerc = secondPerc = "%";
						if (firstPerc.Length > 0 || secondPerc.Length > 0)
							c = "like";
									   
						if(f.get("field") != null && (string)f.get("field") != "")
						{
							sql.AppendFormat(" {0} {1} '{3}{2}{4}'", f.get("field"), c, f.get("value"), firstPerc, secondPerc);
							if(i < list.numElements)
								sql.Append(criteria.get("filterIsAnd").Equals(true)?" AND":"  OR");
						}
					}
				}
			]]></code>
	</Sql>
	<Param name="companyId" type="UniqueIdentifier" />
	<Param name="cacheName" type="UnicodeVarChar" />
</AdoNetDataAccess>
